Tip |
A stored procedure may receive input values as shown in the example below. Un procedimiento almacenado puede recibir valores de entrada como se ilustra en el ejemplo de abajo. |
Problem 1 |
city_bank > Test the following stored procedure in Oracle. Pruebe el siguiente procedimiento almacenado en Oracle. |
city_bank.sql |
CREATE OR REPLACE PROCEDURE p_duplicate ( x NUMBER ) IS BEGIN dbms_output.put_line('The double of : ' || x || ' is ' || 2*x); END p_duplicate; / |
MSDOS: cmd.exe |
SQL> connect city_bank Enter password: Connected. SQL> SET SERVEROUTPUT ON; SQL> EXECUTE p_duplicate(4); The double of : 4 is 8 PL/SQL procedure successfully completed. |
Tip |
When declaring parameters that are passed to the procedure, you do not need to specify the size of the variable. i.e. cost NUMBER is used instead of NUMBER(8, 2). However, in Microsoft SQL Server, it is recommended that you specify the size, if you prefer to control the precision of the calculation. Cuando se declara el parámetro que se va a pasar al procedimiento, no es necesario especificar el tamaño de la variable de entrada. Por ejemplo para pasar un precio se usa NUMBER en lugar de NUMBER (8, 2). Sin embargo, in Microsoft SQL Server se recomienda especificarlo en caso de desear controlar la precisión de las operaciones. |
Problem 2 |
city_bank > Create the procedure p_duplicate to print the double of the input value using Microsoft SQL Server. Cree el procedimiento p_duplicate para imprimir el doble del valor de entrada usando Microsoft SQL Server. |
city_bank.sql |
CREATE PROCEDURE p_duplicate ( @x DECIMAL ) AS BEGIN PRINT('The double of: ' + CAST(@x AS VARCHAR) + ' is ' + CAST(2*@x AS VARCHAR)); END GO |
Microsoft SQL Server |
USE city_bank; EXECUTE p_duplicate 10; |
Output |
The double of: 10 is 20 |
Tip |
Failing to pass the right number of parameters to a procedure, will result in a run time error. En caso de que se ejecute un procedimiento sin pasarle los parámetros requeridos, el resultado será un error en el momento de ejecución. |
Problem 3 |
city_bank > Test the following stored procedure in Oracle. Pruebe el siguiente procedimiento almacenado en Oracle. |
city_bank.sql |
CREATE OR REPLACE PROCEDURE p_twoinputs ( in_weight NUMBER, in_age INTEGER ) IS BEGIN dbms_output.put_line('Age: ' || in_age); dbms_output.put_line('Weight: ' || in_weight); END p_twoinputs; / |
MSDOS: cmd.exe |
SQL> EXECUTE p_twoinputs(170, 100); Age: 100 Weight: 170 PL/SQL procedure successfully completed. |
Problem 4 |
city_bank > Repeat the previous problem using Microsoft SQL Server. Repita el problema anterior usando Microsoft SQL Server. |
Microsoft SQL Server |
USE city_bank; EXECUTE p_twoinputs 50, 18; |
Output |
Age: 18 Weight: 50.00 |
Tip |
The arguments of a procedure may be: IN, OUT or INOUT. Thus, an OUT argument can be used so that the procedure returns a value. See the code below. Los argumentos de un procedimiento pueden ser del tipo IN, OUT o INOUT. Así un argumento OUT se usa para que el procedimiento regrese un valor. Vea el código de abajo. |
Microsoft SQL Server |
--__________________________ MICROSOFT CREATE PROCEDURE p_expe ( @x DECIMAL, -- IN @y INTEGER OUTPUT ) |
Oracle |
--__________________________ ORACLE CREATE OR REPLACE PROCEDURE p_expe ( x IN DECIMAL, y OUT INT ) |
Problem 5 |
city_bank > Test the following stored procedure in Oracle. Pruebe el siguiente procedimiento almacenado en Oracle. |
SQL |
-- ________________________________________________ -- in_gallons is an input variable -- p_litters is a procedure variable CREATE OR REPLACE PROCEDURE p_liters ( in_gallons NUMBER ) IS p_liters NUMBER(6, 2); BEGIN p_liters:=in_gallons*3.7854; dbms_output.put_line(in_gallons || ' gallons are ' || p_liters || ' Lt '); END p_liters; / |
MSDOS: cmd.exe |
SQL> EXECUTE p_liters(10); 10 gallons are 37.85 Lt PL/SQL procedure successfully completed. SQL> |
Problem 6 |
city_bank > Repeat the previous problem using Microsoft SQL Server. Repita el problema anterior usando Microsoft SQL Server. |
Microsoft SQL Server |
USE city_bank; EXECUTE p_liters 10; |
Output |
10 gallons are 37.85 Lt |
Problem 7 |
city_bank > Create a stored procedure in Oracle to convert temperature from Fahrenheit to Celsius. Pruebe el siguiente procedimiento almacenado en Oracle para convertir de grados Fahrenheit a grados Celsius. |
MSDOS: cmd.exe |
SQL> EXECUTE p_celsius(43); 43 F is 6.11 C PL/SQL procedure successfully completed. |
Problem 8 |
city_bank > Repeat the previous problem using Microsoft SQL Server. Repita el problema anterior usando Microsoft SQL Server. |
Microsoft SQL Server |
USE city_bank; EXECUTE p_celsius 80; |
Output |
80.00 F is 26.67 C |
Problem 9 |
city_bank > It is possible to call a stored procedure from a SQL script. In the code below the p_duplicate procedure is being called. Do not forget to set to "on" the server output using SET SERVEROUPUT ON. Es posible llamar un procedimiento desde un script SQL. En el ejemplo mostrado debajo se manda llamar el procedimiento p_duplicate previamente creado. No se olvide de activar la salida del servidor usando SET SERVEROUPUT ON. |
duplicate.sql |
SET ECHO OFF SET VERIFY OFF ACCEPT in_numb PROMPT 'What is the number? ' EXECUTE p_duplicate(&in_numb); SET ECHO ON SET VERIFY ON |
MSDOS: cmd.exe |
SQL> @C:\sql\duplicate.sql SQL> SET ECHO OFF What is the number? 11 The double of : 11 is 22 PL/SQL procedure successfully completed. SQL> SET VERIFY ON SQL> |
Problem 10 |
city_bank > Create an Oracle script to call the stored procedure p_celsius previously created. Use: ACCEPT, PROMPT and EXECUTE. Cree un script de Oracle para llamar al procedimiento almacenado p_celsius que se creó previamente. se: ACCEPT, PROMPT and EXECUTE. |
MSDOS: cmd.exe |
SQL> @C:\sql\celsius.sql SQL> SET ECHO OFF How many fahrenheit? 82 82 F is 27.78 C PL/SQL procedure successfully completed. SQL> SET VERIFY ON |
Problem 11 |
city_bank > Create a stored procedure called p_monthly to determine the monthly payment of a loan, given the monthly interest and the number of months. Using: (a) Oracle, (b) Microsoft SQL Server. city_bank > Cree un procedimiento almacenado llamado p_monthly para determinar el pago mensual de un préstamo, dado el interés mensual y el número de meses. Using: (a) Oracle, (b) Microsoft SQL Server. |
MSDOS: cmd.exe |
SQL> EXECUTE p_monthly(10000, 0.05, 10); The monthly payment is 1295.039 PL/SQL procedure successfully completed. |
Microsoft SQL Server |
USE city_bank; EXECUTE p_monthly 10000, 0.05, 10; |
Output |
The monthly payment is 1295.0390 |